# 要求 1.统计10个Excel，算出每个省份同学的平均分 2. 找到每个省份前三名的同学
import pandas as pd
import os
# 读十个Excel表格内容
# 先将路径组成列表，遍历出每个路径
#再遍历每个文件，读取10个表格内容

def average_score(path):
    for i in range(1, 11):
        df = pd.read_excel(path)
        df['总分'] = df['语文'] + df['数学'] + df['英语']  # 三列求和相加
        df1 = df[['省份', '语文', '数学', '英语', '总分']]
        average_scores = df1.groupby('省份').agg(['mean'])  # 以省份分组三科平均分
        average_scores_df = average_scores.round(2)
        with pd.ExcelWriter(path, mode='a') as writer:
            average_scores_df.to_excel(writer, sheet_name='各省份平均分',index=False)#缺少一个参数if_sheet_exists='replace'



if __name__ == '__main__':
    path1 = 'D:\ITAudit\pandas_excel\pc'
    excels = [fname for fname in os.listdir(path1)]
    for excel_path in excels:
        final_path = path1 + f'\\{excel_path}'
        # print(final_path)
        average_score(final_path)








